using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class Template : ITemplate
    {
        // Methods
        public void Delete(int tem_id)
        {
            string cmdText = "UPDATE tem_info SET isdel='1' WHERE Tem_ID = " + tem_id;
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public int getMaxTemID()
        {
            SqlParameter[] cmdParms = new SqlParameter[0];
            string cmdText = "select max(tem_id) from tem_info";
            return (int)SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms);
        }

        public IList getTemList()
        {
            string cmdText = "SELECT tem_id,tem_type,menu_uid,tem_name,tem_content,create_date FROM tem_info where isdel='0' order by tem_name";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    TemInfo info = new TemInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetDateTime(5).ToShortDateString());
                    list.Add(info);
                }
            }
            return list;
        }

        public TemInfo getTemplateInfo(int tem_id)
        {
            string cmdText = "SELECT tem_id,tem_type,menu_uid,tem_name,tem_content,create_date FROM tem_info where tem_id=" + tem_id;
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            TemInfo info = null;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    info = new TemInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetDateTime(5).ToShortDateString());
                }
            }
            return info;
        }

        public TemInfo GetTemplateInfo(int menu_uid, int con_id)
        {
            if (con_id > 0)
            {
                SysMenu menu = new SysMenu();
                if (menu.IsCnOrEnMenuByName("search_id", menu_uid))
                {
                    int cnOrEnMenuId = menu.GetCnOrEnMenuId("search_detail_template_id", menu_uid);
                    return this.getTemplateInfo(cnOrEnMenuId);
                }
            }
            ContentClass class2 = new ContentClass();
            TemInfo info = null;
            if (con_id > 0)
            {
                ConInfo info2 = class2.getContentInfo(con_id);
                if (null == info2)
                {
                    info = this.getTemplateInfo(ConfigUtil.GetIntValue("error_template_id"));
                }
                else
                {
                    info = this.getTemplateInfo(info2.Tem_ID);
                }
                return info;
            }
            SysMenuInfo sysMenuDataByMID = new SysMenu().GetSysMenuDataByMID(menu_uid);
            if (null == sysMenuDataByMID)
            {
                info = this.getTemplateInfo(ConfigUtil.GetIntValue("error_template_id"));
            }
            else
            {
                info = this.getTemplateInfo(sysMenuDataByMID.Tem_ID);
            }
            return info;
        }

        public IList getTemplateList(int Menu_UID)
        {
            string cmdText = "SELECT tem_id,tem_type,menu_uid,tem_name,tem_content,create_date FROM tem_info where menu_uid=" + Menu_UID + "and isdel='0' order by tem_name";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    TemInfo info = new TemInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetDateTime(5).ToShortDateString());
                    list.Add(info);
                }
            }
            return list;
        }

        public void Insert(TemInfo teminfo)
        {
            string cmdText = string.Concat(new object[] { "INSERT INTO tem_info(tem_type,menu_uid,tem_name,tem_content,create_date,isdel) VALUES(", teminfo.Tem_Type, ", ", teminfo.Menu_UID, ",'", StringUtil.ReplaceStr(teminfo.Tem_Name), "','", StringUtil.ReplaceStr(teminfo.Tem_Content), "',getdate(),'0')" });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public void Update(TemInfo teminfo)
        {
            string cmdText = string.Concat(new object[] { "UPDATE tem_info SET tem_name ='", StringUtil.ReplaceStr(teminfo.Tem_Name), "',tem_content = '", StringUtil.ReplaceStr(teminfo.Tem_Content), "' WHERE Tem_ID =", teminfo.Tem_ID });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}